﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.IO.Ports;

namespace TCA_User
{
    public partial class Bill_List
    {
        //private string TCA_Database_Conn = @"server=NGUYENTRUNGKIEN\SQLEXPRESS;database=TCA_DATABASE;uid=sa;pwd=123456";
        private string TCA_Database_Conn = @"server=VNMSRV607\SQLEXPRESS;database=TCA_DATABASE;uid=sa2;pwd=123456!@#";
        enum BILL_STATUS
        {
            BILL_FREE = 0,
            BILL_LOCKED = 1,
            BILL_APRROVED = 2
        };

        enum BILL_EXIST
        {
            NEW_BILL = 0,
            BILL_SAME_CARD = 1,
            BILL_OTHER_CARD = 2
        };

        DataTable Taxi_List_dtb = new DataTable();
        DataSet Taxi_List_ds = new DataSet();
        SqlDataAdapter Taxi_List_da;

        DataTable Card_List_Each_Provider_dtb = new DataTable();
        DataSet Card_List_Each_Provider_ds = new DataSet();
        SqlDataAdapter Card_List_Each_Provider_da;

        DataTable Card_List_dtb = new DataTable();
        DataSet Card_List_ds = new DataSet();
        SqlDataAdapter Card_List_da;

        //DataTable Deparment_Card_List_dtb = new DataTable();
        //DataSet Deparment_Card_List_ds = new DataSet();
        //SqlDataAdapter Deparment_Card_List_da;

        DataTable Deparment_Account_dtb = new DataTable();
        DataSet Deparment_Account_ds = new DataSet();
        SqlDataAdapter Deparment_Account_da;

        DataTable BillTab_CardOwner_Tbl;

        /*******************************************
         * CARD OWNER                              * 
         * *****************************************/
        private DataTable Load_CardOwner_List(ref SqlDataAdapter dataAdapter, ref DataSet input_dataset)
        {
            DataTable dtbTmp = new DataTable();
            string department = User_Info.Department.ToString().Trim();
            string permission = User_Info.Permission.ToString().Trim();
            string dept_cmd = "";
            string get_CardOwner_list_cmd = @"SELECT * FROM Account_TBL";
            if (permission == ADMIN_LVL)
            {
            }
            else if (permission == MANAGER_LVL)
            {
                dept_cmd = " WHERE DEPARTMEMT_NAME = '" + department + "'";
                get_CardOwner_list_cmd += dept_cmd;
            }
            else {
                dept_cmd = " WHERE UserName = '" + User_Info.UserName.ToString().Trim() + "'";
                get_CardOwner_list_cmd += dept_cmd;
            }
            dtbTmp = Get_SQL_Data(TCA_Database_Conn, get_CardOwner_list_cmd, ref dataAdapter, ref input_dataset);
            return dtbTmp;
        }

        private void Load_CardOwner()
        {
            DataSet card_owner_list_dataset = new DataSet();
            SqlDataAdapter card_owner_list_dataAdapter = new SqlDataAdapter();
            BillTab_CardOwner_Tbl = Load_CardOwner_List(ref card_owner_list_dataAdapter, ref card_owner_list_dataset);
        }

        /*******************************************
         * TAXI PROVIDERLIST                       * 
         * *****************************************/
        private void Load_Taxi_List()
        {
            string sql_cmd = @"SELECT * FROM dbo.[Taxi_TBL]";
            Taxi_List_dtb.Clear();
            Taxi_List_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref Taxi_List_da, ref Taxi_List_ds);
            Taxi_comboBox.DisplayMember = "Taxi_Provider_Name";
            Taxi_comboBox.ValueMember = "Taxi_Provider_Name";
            Taxi_comboBox.DataSource = Taxi_List_dtb;
        }
        // End Taxi List


        /*******************************************
         * CARD LIST PROVIDERLIST                  * 
         * *****************************************/
        private void Load_Card_List()
        {
            string sql_cmd;
            string card_user_list = "";
            int i = 0;

            if (User_Info.Permission == ADMIN_LVL)
            {
                if (BillTab_CardOwnerList.Enabled == true)
                {
                    sql_cmd = @"SELECT * FROM dbo.[Cards_TBL] WHERE Card_Owner = '" + BillTab_CardOwnerList.Text.ToString() + "'";
                }
                else
                {
                    sql_cmd = @"SELECT * FROM dbo.[Cards_TBL]";
                }
            }
            else if (User_Info.Permission == MANAGER_LVL)
            {
                if (BillTab_CardOwnerList.Enabled == false)
                {
                    sql_cmd = @"SELECT * FROM dbo.[Cards_TBL]";
                    // WHERE Card_Owner = '" + BillTab_CardOwnerList.Text.ToString() + "'";
                    i = 0;
                    foreach (DataRow row in BillTab_CardOwner_Tbl.Rows )
                    {
                        if (i == 0)
                        {
                            card_user_list = " WHERE Card_Owner = '" + row["UserName"].ToString().Trim() + "'";
                        }
                        else  
                        {
                            card_user_list += " OR Card_Owner = '" + row["UserName"].ToString().Trim() + "'";
                        }
                        i++;
                    }
                }
                else
                {
                    sql_cmd = @"SELECT * FROM dbo.[Cards_TBL] WHERE Card_Owner = '" + BillTab_CardOwnerList.Text.ToString() + "'";
                }
            }
            else 
            {
                Card_List_dtb.Clear();
                return;
            }

            Card_List_dtb.Clear();
            Card_List_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref Card_List_da, ref Card_List_ds);
            Bill_Tab_AllCardList.DataSource = Card_List_dtb;
            Bill_Tab_AllCardList.DisplayMember = "Card_Provider_Name";
            Bill_Tab_AllCardList.ValueMember = "Card_Provider_Name";
        }

        private DataTable Load_Personal_Card_List(string username)
        {
            string sql_cmd;
            DataTable personal_card_list;
            DataSet personal_card_list_dataset = new DataSet();
            SqlDataAdapter personal_card_list_dataAdapter = new SqlDataAdapter();

            sql_cmd = @"SELECT * FROM dbo.[Cards_TBL] WHERE Card_Owner = '" + username + "'";

            personal_card_list = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref personal_card_list_dataAdapter, ref personal_card_list_dataset);
            return personal_card_list;
        }

        private bool Load_Department_Account(string department)
        {
            string sql_cmd;

            if (User_Info.Permission == ADMIN_LVL)
            {
                sql_cmd = @"SELECT * FROM dbo.[Account_TBL] WHERE DEPARTMEMT_NAME = '" + department + "'";
            }
            else
            {
                return false;
            }

            Deparment_Account_dtb.Clear();
            Deparment_Account_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref Deparment_Account_da, ref Deparment_Account_ds);
            return true;
        }

        private void Load_Card_List_EachProvider()
        {
            string sql_cmd;

            sql_cmd = @"SELECT * FROM dbo.[Cards_TBL]";
            sql_cmd = sql_cmd + " WHERE Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            
            Card_List_Each_Provider_dtb.Clear();
            Card_List_Each_Provider_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref Card_List_Each_Provider_da, ref Card_List_Each_Provider_ds);
            CardNo_comboBox.DisplayMember = "Card_Provider_Name";
            CardNo_comboBox.ValueMember = "Card_Provider_Name";
            CardNo_comboBox.DataSource = Card_List_Each_Provider_dtb;
        }

        private void Load_VSCardList()
        {
            string sql_cmd;

            sql_cmd = @"SELECT * FROM dbo.[Cards_TBL]";
            sql_cmd = sql_cmd + " WHERE Taxi_Provider_Name = 'VinaSun'";

            VS_Card_dtb.Clear();
            VS_Card_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref VS_Card_da, ref VS_Card_ds);
        }

        private void Load_MLCardList()
        {
            string sql_cmd;

            sql_cmd = @"SELECT * FROM dbo.[Cards_TBL]";
            sql_cmd = sql_cmd + " WHERE Taxi_Provider_Name = 'MaiLinh'";

            ML_Card_dtb.Clear();
            ML_Card_dtb = Get_SQL_Data(TCA_Database_Conn, sql_cmd, ref ML_Card_da, ref ML_Card_ds);
        }
        //End card List
        

        /************************************************
         * BILL LIST MANAGE
         * **********************************************/

        private BILL_EXIST Check_Bill_Exits_Database(string bill_id, string taxi_provider_name)
        {
            string sql_cmd_str = @"SELECT * FROM [Taxi_Bills_TBL] WHERE ";
            //string value = "Bill_ID = '" + BillNo_txt.Text + "' AND Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            string value = "Bill_ID = '" + bill_id + "' AND Taxi_Provider_Name = '" + taxi_provider_name + "'";
            BILL_EXIST retvar = BILL_EXIST.NEW_BILL;
            string card_ID, new_card_id;

            sql_cmd_str = sql_cmd_str + value;
            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand get_empl_cmd = new SqlCommand(sql_cmd_str, conn);
                SqlDataReader reader = get_empl_cmd.ExecuteReader();
                // Data
                if (reader.Read() == true)
                {
                    card_ID = reader["Card_Provider_Name"].ToString().Trim();
                    new_card_id = CardNo_comboBox.Text.Trim();
                    if (card_ID != new_card_id)
                    {
                        MessageBox.Show("Can not Apply for this Bill.\nThis Bill already commit with card ID: " + card_ID, "Bill Exist");
                        retvar = BILL_EXIST.BILL_OTHER_CARD;
                    }
                    else {
                        retvar = BILL_EXIST.BILL_SAME_CARD;
                    }
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return retvar;
        }
        private BILL_STATUS Get_Bill_Status()
        {
            string sql_cmd_str = @"SELECT * FROM [Taxi_Bills_TBL] WHERE ";
            string value = "Bill_ID = '" + BillNo_txt.Text + "' AND Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            string bill_locked;
            string bill_aprrove;
            BILL_STATUS status = BILL_STATUS.BILL_FREE;

            sql_cmd_str = sql_cmd_str + value;
            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand get_empl_cmd = new SqlCommand(sql_cmd_str, conn);
                SqlDataReader reader = get_empl_cmd.ExecuteReader();
                // Data
                if (reader.Read() == true)
                {
                    bill_aprrove = reader["Approved"].ToString().Trim();
                    bill_locked = reader["Locked"].ToString().Trim();
                    if (bill_locked == "Y")
                    {
                        status = BILL_STATUS.BILL_LOCKED;
                    }else if (bill_aprrove == "Y")
                    {
                        status = BILL_STATUS.BILL_APRROVED;
                    }
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return status;
        }
        
        private bool Insert_New_Bill()
        {
            string sql_cmd = @"INSERT INTO [Taxi_Bills_TBL] 
                                          ([Bill_ID]
                                          ,[Taxi_Provider_Name]
                                          ,[UserName]
                                          ,[DEPARTMEMT_NAME] 
                                          ,[Bill_date]
                                          ,[Card_Provider_Name]
                                          ,[Taxi_ID]
                                          ,[Route]
                                          ,[Fee]
                                          ,[Others_Fee]
                                          ,[Business]
                                          ,[Bill_Note]
                                          ,[Change_by]
                                          ,[Change_DateTime]
                                          ,[Change_type]) VALUES ";

            string value;
            value = "('" + BillNo_txt.Text + "', ";
            value = value + "'" + Taxi_comboBox.Text + "', ";
            value = value + "'" + User_Info.UserName.ToString().Trim() + "','" + User_Info.Department.ToString().Trim() + "',";
            value = value + "'" + Bill_dateTimePicker.Value.Date.ToString("dd MMM yy") + "', ";
            value = value + "'" + CardNo_comboBox.Text + "', ";
            value = value + "'" + TaxiID_txt.Text + "', ";
            value = value + "'" + Route_txt.Text + "', ";
            value = value + "'" + TaxiFee_txt.Text + "', ";
            value = value + "'" + OtherFee_txt.Text + "', ";
            if (Business_check.Checked == true)
            {
                value = value + "'Y', ";
            }
            else {
                value = value + "'N', ";
            }
            value = value + "'" + Note_richTextBox.Text + "', ";
            value = value + "'" + User_Info.UserName + "', ";
            value = value + "'" + DateTime.Now.ToString() + "', ";
            value = value + "'Add New')";

            sql_cmd = sql_cmd + value;
            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand insert_sql = new SqlCommand(sql_cmd, conn);
                insert_sql.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }

        private bool Update_Bill_Info()
        {
            string sql_cmd = @"UPDATE [Taxi_Bills_TBL] 
                                SET ";/*column1=value, 
                                        column2=value2,...
                                        WHERE some_column = some_value ";*/

            string value = "";

            value = value + "Bill_date = '" + Bill_dateTimePicker.Value.Date.ToString("dd MMM yyyy") + "', ";
            value = value + "Card_Provider_Name = '" + CardNo_comboBox.Text + "', ";
            value = value + "Taxi_ID = '" + TaxiID_txt.Text + "', ";
            value = value + "Route = '" + Route_txt.Text + "', ";
            value = value + "Fee = '" + TaxiFee_txt.Text + "', ";
            value = value + "Others_Fee = '" + OtherFee_txt.Text + "', ";
            if (Business_check.Checked == true)
            {
                value = value + "Business = 'Y', ";
            }
            else
            {
                value = value + "Business = 'N', ";
            }
            value = value + "Bill_Note = '" + Note_richTextBox.Text + "', ";
            value = value + "Change_by = '" + User_Info.UserName + "', ";
            value = value + "Change_DateTime = '" + DateTime.Now.ToString("dd MMM yy") + "', ";
            value = value + "Change_type = 'Update'";
            sql_cmd = sql_cmd + value + " WHERE ";
            //WHERE
            value = "Bill_ID = '" + BillNo_txt.Text + "' ";
            value = value + "AND Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            sql_cmd = sql_cmd + value;

            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand update_sql = new SqlCommand(sql_cmd, conn);
                update_sql.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }

        private bool Approve_Bill()
        {
            string sql_cmd = @"UPDATE [Taxi_Bills_TBL] 
                                SET ";/*column1=value, 
                                    column2=value2,...
                                WHERE some_column = some_value ";*/
            string value = "";

            value = value + "Approved = 'Y'";
            value = value + " WHERE Bill_ID = '" + BillNo_txt.Text + "' ";
            value = value + "AND Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            sql_cmd = sql_cmd + value;

            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand update_sql = new SqlCommand(sql_cmd, conn);
                update_sql.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }
        private bool Lock_Bill()
        {
            string sql_cmd = @"UPDATE [Taxi_Bills_TBL] 
                                SET ";/*column1=value, 
                                    column2=value2,...
                                WHERE some_column = some_value ";*/
            string value = "";

            value = value + "Locked = 'Y'";
            value = value + " WHERE Bill_ID = '" + BillNo_txt.Text + "' ";
            value = value + "AND Taxi_Provider_Name = '" + Taxi_comboBox.Text + "'";
            sql_cmd = sql_cmd + value;

            // Tạo connection
            System.Data.SqlClient.SqlConnection conn = new SqlConnection(TCA_Database_Conn);
            try
            {
                conn.Open();
                // Get data from Database
                SqlCommand update_sql = new SqlCommand(sql_cmd, conn);
                update_sql.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return true;
        }
        //End BILL LIST MANAGE

        string GetCardUser(string card_name, DateTime used_date)
        {
            DateTime star_date, end_date;
            string username = "";
            DateTime star_date_t, end_date_t;
            string filterExpression = "Card_Provider_Name = '" + card_name.Trim() + "'";
            DataRow[] card_borrow = Card_Borrow_Tbl.Data_dtb.Select(filterExpression);

            foreach (DataRow row in card_borrow)
            {
                star_date = (DateTime) row["FromDate"];
                end_date = (DateTime)row["ToDate"];
                if ((used_date >= star_date) && (used_date <= end_date))
                {
                    if ((username != "") && (username != row["UserName"].ToString().Trim()))
                    {
                        MessageBox.Show("duplicate user use card: " + card_name 
                                        + " /nAt the same date: " + used_date.Date.ToString()
                                        + "/nUser: " + username + " & " + row["UserName"].ToString().Trim(), "warning");
                        return "";
                    }
                    username = row["UserName"].ToString().Trim();
                    star_date_t = star_date;
                    end_date_t = end_date;

                }
            }
            if (username == "")
            {
                foreach (DataRow row in Card_Table_Form.Data_dtb.Rows)
                {
                    if (card_name == row["Card_Provider_Name"].ToString().Trim())
                    {
                        username = row["Card_Owner"].ToString().Trim();
                        break;
                    }
                }
            }
            return username;
        }

        string GetDepartment(string user_name)
        {
            string department = "";
            foreach (DataRow row in Account_TBL_Form.Data_dtb.Rows)
            {
                if (user_name == row["UserName"].ToString().Trim())
                {
                    department = row["DEPARTMEMT_NAME"].ToString().Trim();
                    break;
                }
            }
            return department;
        }
    }
}
